package guda.house.web.action.stat;

import guda.grape.autogen.common.page.BizResult;
import guda.house.biz.BillStatBiz;
import guda.house.biz.BillStatOtherBiz;
import guda.house.biz.beans.vo.*;
import guda.house.biz.form.bill.AccountEstateSearchForm;
import guda.house.biz.form.stat.StatFeeForm;
import guda.house.biz.query.AccountEstateQuery;
import guda.house.biz.security.AppUtil;
import guda.house.biz.util.DateUtil;
import guda.house.biz.util.ExportExcelUtil;
import guda.house.biz.util.PriceUtil;
import guda.house.dal.dao.BuildingDAO;
import guda.house.dal.dao.OfficeEmployeeDAO;
import guda.house.dal.dao.SysVarDAO;
import guda.house.dal.dataobject.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * Created by well on 16/2/27.
 */
@Controller
public class StatExportAction {

    @Autowired
    private BillStatBiz billStatBiz;
    @Autowired
    private SysVarDAO sysVarDAO;
    @Autowired
    private BuildingDAO buildingDAO;
    @Autowired
    private OfficeEmployeeDAO officeEmployeeDAO;
    @Autowired
    private BillStatOtherBiz billStatOtherBiz;



    @RequestMapping(value = "export/statCharge.do", method = RequestMethod.GET)
    public void exportStatCharge(HttpServletResponse response, ModelMap modelMap,StatFeeForm statFeeForm) {
        statFeeForm.setPageSize(10000);
        BizResult stat = billStatBiz.statCharge(statFeeForm);
        List<BillPayDO> billPayDOs = (List<BillPayDO>)stat.data.get("billPayList");

        List<BillPayXls> billPayXlsList =  new ArrayList<BillPayXls>(billPayDOs.size());
        for(BillPayDO billPayDO:billPayDOs){

            BillPayXls billPayXls = new BillPayXls();
            billPayXls.setGmtCreate(DateUtil.formatYMD(billPayDO.getGmtCreate()));
            billPayXls.setPayTotal(PriceUtil.formatCNY(billPayDO.getPayTotal()));
            billPayXls.setPayTotalCharge(PriceUtil.formatCNY(billPayDO.getPayTotalCharge()));
            billPayXls.setPayType(billPayDO.getPayType());
            billPayXls.setVillageName(AppUtil.getVillageName(statFeeForm.getVillageId()));
            billPayXlsList.add((billPayXls));

        }
        String filedisplay = null;
        try {
            filedisplay = URLEncoder.encode("手续费统计.xls", "UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.setContentType("application/x-download; charset=UTF-8");
        response.addHeader("Content-Disposition", "attachment; filename=" + filedisplay);
        String[] headers = new String[]{"交易时间","交易金额","支付方式","手续费","备注","所属小区"};

        String[] fields = new String[]{"gmtCreate","payTotal","payType","payTotalCharge","payComment","villageName"};
        ServletOutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
            ExportExcelUtil.exportExcel("手续费统计", headers, fields, billPayXlsList, outputStream);
            outputStream.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

    @RequestMapping(value = "export/statEstateDiscount.do", method = RequestMethod.GET)
    public void exportStatEstateDiscount(HttpServletResponse response, ModelMap modelMap,StatFeeForm statFeeForm) {
        statFeeForm.setPageSize(10000);
        BizResult stat = billStatOtherBiz.statEstateDiscount(statFeeForm);
        List<StatEstateDiscountVO> statEstateDiscountVOList = (List<StatEstateDiscountVO>)stat.data.get("statEstateDiscountVOList");

        List<StatEstateDiscountXls> statEstateDiscountXlsList =  new ArrayList<StatEstateDiscountXls>(statEstateDiscountVOList.size());
        for(StatEstateDiscountVO statEstateDiscountVO:statEstateDiscountVOList){

            StatEstateDiscountXls statEstateDiscountXls = new StatEstateDiscountXls();
            statEstateDiscountXls.setDiscountType(statEstateDiscountVO.getDiscountType());
            statEstateDiscountXls.setDiscount(PriceUtil.formatCNY(statEstateDiscountVO.getDiscount()));
            statEstateDiscountXls.setOwnerName(statEstateDiscountVO.getOwnerName());
            statEstateDiscountXls.setSuiteName(statEstateDiscountVO.getSuiteName());
            statEstateDiscountXlsList.add((statEstateDiscountXls));

        }
        String filedisplay = null;
        try {
            filedisplay = URLEncoder.encode("地产减免物业费统计.xls", "UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.setContentType("application/x-download; charset=UTF-8");
        response.addHeader("Content-Disposition", "attachment; filename=" + filedisplay);
        String[] headers = new String[]{"房号","姓名","减免总金额","减免类型"};


        String[] fields = new String[]{"suiteName","ownerName","discount","discountType"};
        ServletOutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
            ExportExcelUtil.exportExcel("地产减免物业费统计", headers, fields, statEstateDiscountXlsList, outputStream);
            outputStream.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }


    @RequestMapping(value = "export/statInvoice.do", method = RequestMethod.GET)
    public void exportStatInvoice(HttpServletResponse response, ModelMap modelMap,StatFeeForm statFeeForm) {
        statFeeForm.setPageSize(10000);
        BizResult stat = billStatOtherBiz.statInvoice(statFeeForm);
        List<InvoiceLogDO> invoiceLogDOs = (List<InvoiceLogDO>)stat.data.get("invoiceLogDOList");

        List<InvoiceLogXls> invoiceLogXlsList =  new ArrayList<InvoiceLogXls>(invoiceLogDOs.size());
        for(InvoiceLogDO invoiceLogDO:invoiceLogDOs){

            InvoiceLogXls invoiceLogXls = new InvoiceLogXls();
            invoiceLogXls.setGmtInvoice(DateUtil.formatYMD(invoiceLogDO.getGmtInvoice()));
            invoiceLogXls.setInvoiceMoney(PriceUtil.formatCNY(invoiceLogDO.getInvoiceMoney()));
            invoiceLogXls.setInvoiceNo(invoiceLogDO.getInvoiceNo());
            invoiceLogXls.setInvoiceSerial(invoiceLogDO.getInvoiceSerial());
            invoiceLogXlsList.add((invoiceLogXls));

        }
        String filedisplay = null;
        try {
            filedisplay = URLEncoder.encode("票据管理统计.xls", "UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.setContentType("application/x-download; charset=UTF-8");
        response.addHeader("Content-Disposition", "attachment; filename=" + filedisplay);
        String[] headers = new String[]{"票据序号","发票号码","开票日期","金额"};

        String[] fields = new String[]{"key","total","countDelivery","countDecoration","countCheckIn"};
        ServletOutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
            ExportExcelUtil.exportExcel("票据管理统计", headers, fields, invoiceLogXlsList, outputStream);
            outputStream.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }


    @RequestMapping(value = "export/statDelivery.do", method = RequestMethod.GET)
    public void exportStatDelivery(HttpServletResponse response, ModelMap modelMap,StatFeeForm statFeeForm) {
        BizResult stat = billStatBiz.statDelivery(statFeeForm);
        Map<String, SuiteDeliveryVO> map = (Map<String,SuiteDeliveryVO>)stat.data.get("map");
        Iterator<Map.Entry<String, SuiteDeliveryVO>> iterator = map.entrySet().iterator();
        List<SuiteDeliveryXls> suiteDeliveryXlsList =  new ArrayList<SuiteDeliveryXls>(map.size());
        while(iterator.hasNext()){
            Map.Entry<String, SuiteDeliveryVO> next = iterator.next();
            SuiteDeliveryVO suiteDeliveryVO = next.getValue();
            SuiteDeliveryXls suiteDeliveryXls = new SuiteDeliveryXls();
            suiteDeliveryXls.setKey(next.getKey());
            suiteDeliveryXls.setTotal(String.valueOf(suiteDeliveryVO.getTotal()));
            suiteDeliveryXls.setCountCheckIn(String.valueOf(suiteDeliveryVO.getCountCheckIn()));
            suiteDeliveryXls.setCountDecoration(String.valueOf(suiteDeliveryVO.getCountDecoration()));
            suiteDeliveryXls.setCountDelivery(String.valueOf(suiteDeliveryVO.getCountDelivery()));

            suiteDeliveryXlsList.add((suiteDeliveryXls));

        }
        String filedisplay = null;
        try {
            filedisplay = URLEncoder.encode("交房统计.xls", "UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.setContentType("application/x-download; charset=UTF-8");
        response.addHeader("Content-Disposition", "attachment; filename=" + filedisplay);
        String[] headers = new String[]{"分组","房屋数","交房数","装修数","入住数"};

        String[] fields = new String[]{"key","total","countDelivery","countDecoration","countCheckIn"};
        ServletOutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
            ExportExcelUtil.exportExcel("交房统计", headers, fields, suiteDeliveryXlsList, outputStream);
            outputStream.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }


    @RequestMapping(value = "export/estateRateCompare.do", method = RequestMethod.GET)
    public void exportEstateRateCompare(HttpServletResponse response, ModelMap modelMap,StatFeeForm statFeeForm) {
        BizResult stat = billStatBiz.estateRateCompare(statFeeForm);
        Map<String,EstateRateCompareVO> map = (Map<String,EstateRateCompareVO>)stat.data.get("map");
        Iterator<Map.Entry<String, EstateRateCompareVO>> iterator = map.entrySet().iterator();
        List<EstateRateCompareXlsVO> estateRateCompareXlsVOList =  new ArrayList<EstateRateCompareXlsVO>(map.size());
        while(iterator.hasNext()){
            Map.Entry<String, EstateRateCompareVO> next = iterator.next();
            EstateRateCompareVO estateRateCompareVO = next.getValue();
            EstateRateCompareXlsVO estateRateCompareXlsVO = new EstateRateCompareXlsVO();
            estateRateCompareXlsVO.setKey(next.getKey());
            estateRateCompareXlsVO.setReceivableNow(PriceUtil.formatCNY(estateRateCompareVO.getReceivableNow()));
            estateRateCompareXlsVO.setPaidNow(PriceUtil.formatCNY(estateRateCompareVO.getPaidNow()));
            estateRateCompareXlsVO.setRateNow((estateRateCompareVO.getRateNow()));
            estateRateCompareXlsVO.setRateLastYear((estateRateCompareVO.getRateLastYear()));
            estateRateCompareXlsVO.setReceivableLastYear(PriceUtil.formatCNY(estateRateCompareVO.getReceivableLastYear()));
            estateRateCompareXlsVO.setReceivableDiff(PriceUtil.formatCNY(estateRateCompareVO.getReceivableDiff()));
            estateRateCompareXlsVO.setPaidLastYear(PriceUtil.formatCNY(estateRateCompareVO.getPaidLastYear()));
            estateRateCompareXlsVO.setPaidDiff(PriceUtil.formatCNY(estateRateCompareVO.getPaidDiff()));
            estateRateCompareXlsVOList.add((estateRateCompareXlsVO));

        }
        String filedisplay = null;
        try {
            filedisplay = URLEncoder.encode("当期物业费收费率对比.xls", "UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.setContentType("application/x-download; charset=UTF-8");
        response.addHeader("Content-Disposition", "attachment; filename=" + filedisplay);
        String[] headers = new String[]{"分组","当期应收","当期实收","当期收费率","去年同期收费率","去年应收","应收差异","去年实收","实收差异"};

        String[] fields = new String[]{"key","receivableNow","paidNow","rateNow","rateLastYear","receivableLastYear","receivableDiff","paidLastYear","paidDiff"};
        ServletOutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
            ExportExcelUtil.exportExcel("当期物业费收费率对比", headers, fields, estateRateCompareXlsVOList, outputStream);
            outputStream.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }


    @RequestMapping(value = "export/waterElecRate.do", method = RequestMethod.GET)
    public void exportwaterElecRate(HttpServletResponse response, ModelMap modelMap,StatFeeForm statFeeForm) {
        BizResult stat = billStatBiz.statWaterElec(statFeeForm);
        Map<String, BillRateVO> billRateVOMap = (Map<String, BillRateVO>)stat.data.get("billRateVOMap");
        Iterator<Map.Entry<String, BillRateVO>> iterator = billRateVOMap.entrySet().iterator();
        List<EstateRateXls> estateRateXlsList =  new ArrayList<EstateRateXls>(billRateVOMap.size());
        while(iterator.hasNext()){
            Map.Entry<String, BillRateVO> next = iterator.next();
            BillRateVO billRateVO = next.getValue();
            EstateRateXls estateRateXls = new EstateRateXls();
            estateRateXls.setKey(next.getKey());
            estateRateXls.setUnpayTotal(PriceUtil.formatCNY(billRateVO.getUnpayTotal()));
            estateRateXls.setTotal(PriceUtil.formatCNY(billRateVO.getTotal()));
            estateRateXls.setPayed(PriceUtil.formatCNY(billRateVO.getPayed()));
            estateRateXls.setPreTotal(PriceUtil.formatCNY(billRateVO.getPreTotal()));
            estateRateXls.setPreTotalNextYear(PriceUtil.formatCNY(billRateVO.getPreTotalNextYear()));
            estateRateXls.setRate(billRateVO.getRate());
            estateRateXlsList.add(estateRateXls);

        }
        String filedisplay = null;
        try {
            filedisplay = URLEncoder.encode("水电费收费率.xls", "UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.setContentType("application/x-download; charset=UTF-8");
        response.addHeader("Content-Disposition", "attachment; filename=" + filedisplay);
        String[] headers = new String[]{"分组","历年欠费金额","当期应收","当期实收","当期预收","次年预收","当期收费率"};

        String[] fields = new String[]{"key","unpayTotal","total","payed","preTotal","preTotalNextYear","rate"};
        ServletOutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
            ExportExcelUtil.exportExcel("水电费收费率", headers, fields, estateRateXlsList, outputStream);
            outputStream.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }



    @RequestMapping(value = "export/estateRateLastYear.do", method = RequestMethod.GET)
    public void exportestateRateLastYear(HttpServletResponse response, ModelMap modelMap,StatFeeForm statFeeForm) {
        BizResult stat = billStatBiz.statEstateLastYear(statFeeForm);
        Map<String, BillLastYearRateVO> billLastYearRateVOMap = (Map<String, BillLastYearRateVO>)stat.data.get("billLastYearRateVOMap");
        Iterator<Map.Entry<String, BillLastYearRateVO>> iterator = billLastYearRateVOMap.entrySet().iterator();
        List<EstateRateLastYearXls> estateRateLastYearXlsList =  new ArrayList<EstateRateLastYearXls>(billLastYearRateVOMap.size());
        while(iterator.hasNext()){
            Map.Entry<String, BillLastYearRateVO> next = iterator.next();
            BillLastYearRateVO billLastYearRateVO = next.getValue();
            EstateRateLastYearXls estateRateLastYearXls = new EstateRateLastYearXls();
            estateRateLastYearXls.setKey(next.getKey());
            estateRateLastYearXls.setUnpayTotalLastYear(PriceUtil.formatCNY(billLastYearRateVO.getUnpayTotalLastYear()));
            estateRateLastYearXls.setPayedTotalLastYear(PriceUtil.formatCNY(billLastYearRateVO.getPayedTotalLastYear()));
            estateRateLastYearXls.setRateLastYear((billLastYearRateVO.getRateLastYear()));

            estateRateLastYearXlsList.add(estateRateLastYearXls);

        }
        String filedisplay = null;
        try {
            filedisplay = URLEncoder.encode("收回历年欠费收费率.xls", "UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.setContentType("application/x-download; charset=UTF-8");
        response.addHeader("Content-Disposition", "attachment; filename=" + filedisplay);
        String[] headers = new String[]{"分组","历年欠费金额","收回历年欠费金额","当期收费率"};

        String[] fields = new String[]{"key","unpayTotalLastYear","payedTotalLastYear","rateLastYear"};
        ServletOutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
            ExportExcelUtil.exportExcel("收回历年欠费收费率", headers, fields, estateRateLastYearXlsList, outputStream);
            outputStream.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

    @RequestMapping(value = "export/estateRate.do", method = RequestMethod.GET)
    public void exportEstateRate(HttpServletResponse response, ModelMap modelMap,StatFeeForm statFeeForm) {
        BizResult stat = billStatBiz.statEstate(statFeeForm);
        Map<String, BillRateVO> billRateVOMap = (Map<String, BillRateVO>)stat.data.get("billRateVOMap");
        Iterator<Map.Entry<String, BillRateVO>> iterator = billRateVOMap.entrySet().iterator();
        List<EstateRateXls> estateRateXlsList =  new ArrayList<EstateRateXls>(billRateVOMap.size());
        while(iterator.hasNext()){
            Map.Entry<String, BillRateVO> next = iterator.next();
            BillRateVO billRateVO = next.getValue();
            EstateRateXls estateRateXls = new EstateRateXls();
            estateRateXls.setKey(next.getKey());
            estateRateXls.setUnpayTotal(PriceUtil.formatCNY(billRateVO.getUnpayTotal()));
            estateRateXls.setTotal(PriceUtil.formatCNY(billRateVO.getTotal()));
            estateRateXls.setPayed(PriceUtil.formatCNY(billRateVO.getPayed()));
            estateRateXls.setPreTotal(PriceUtil.formatCNY(billRateVO.getPreTotal()));
            estateRateXls.setPreTotalNextYear(PriceUtil.formatCNY(billRateVO.getPreTotalNextYear()));
            estateRateXls.setRate(billRateVO.getRate());
            estateRateXls.setSelfDiscount(PriceUtil.formatCNY(billRateVO.getSelfDiscount()));
            estateRateXls.setDichanDiscount(PriceUtil.formatCNY(billRateVO.getDichanDiscount()));
            estateRateXls.setSelfDiscountAllYear(PriceUtil.formatCNY(billRateVO.getSelfDiscountAllYear()));
            estateRateXls.setDichanDiscountAllYear(PriceUtil.formatCNY(billRateVO.getDichanDiscountAllYear()));
            estateRateXls.setRateDiscount((billRateVO.getRateDiscount()));
            estateRateXls.setSuiteTotal(String.valueOf(billRateVO.getSuiteTotal()));
            estateRateXls.setSuiteDeliverTotal(String.valueOf(billRateVO.getSuiteDeliverTotal()));
            estateRateXlsList.add(estateRateXls);

        }
        String filedisplay = null;
        try {
            filedisplay = URLEncoder.encode("当期物业费收费率.xls", "UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.setContentType("application/x-download; charset=UTF-8");
        response.addHeader("Content-Disposition", "attachment; filename=" + filedisplay);
        String[] headers = new String[]{"分组","历年欠费金额","当期应收","当期实收","当期预收","次年预收","当期收费率","当期自行减免","当期地产减免","当年自行减免"
                ,"当年地产减免","减免率","总房数","交房数"};

        String[] fields = new String[]{"key","unpayTotal","total","payed","preTotal","preTotalNextYear","rate","selfDiscount"
                ,"dichanDiscount","selfDiscountAllYear","dichanDiscountAllYear","rateDiscount","suiteTotal"
                ,"suiteDeliverTotal"};
        ServletOutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
            ExportExcelUtil.exportExcel("当期物业费收费率", headers, fields, estateRateXlsList, outputStream);
            outputStream.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }


    @RequestMapping(value = "export/tieOff.do", method = RequestMethod.GET)
    public void export(HttpServletResponse response, ModelMap modelMap,StatFeeForm statFeeForm) {
        BizResult stat = billStatBiz.statTieOff(statFeeForm);
        Map<String, BillStatVO> map = (Map<String, BillStatVO>)stat.data.get("statMap");
        List<String> extList = (List<String>)stat.data.get("extList");
        List<SysVarDO> extraList = (List<SysVarDO>)stat.data.get("extraList");
        List<String> accountEstateTypeList = (List<String>)stat.data.get("accountEstateTypeList");
        String filedisplay = null;
        try {
            filedisplay = URLEncoder.encode("日扎帐汇总.xls", "UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.setContentType("application/x-download; charset=UTF-8");
        response.addHeader("Content-Disposition", "attachment; filename=" + filedisplay);

        ServletOutputStream outputStream = null;
        try {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet("日扎帐汇总");

            HSSFCellStyle cellCenterStyle =  workbook.createCellStyle();// 设置表头样式
            cellCenterStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置居中

            HSSFCellStyle borderStyle =  workbook.createCellStyle();// 设置表头样式
            borderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置居中
            borderStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
            borderStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
            borderStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
            borderStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框


            HSSFRow row = sheet.createRow(0);
            HSSFRow row2 = sheet.createRow(1);
            HSSFCell cellTitle = row.createCell(0);
            sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
            cellTitle.setCellValue("日期");
            cellTitle.setCellStyle(borderStyle);

            cellTitle = row.createCell(1);
            sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));
            cellTitle.setCellValue("物业费");
            cellTitle.setCellStyle(borderStyle);

            cellTitle = row.createCell(2);
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 2, 3));
            cellTitle.setCellStyle(borderStyle);
            cellTitle.setCellValue("水费");

            cellTitle = row2.createCell(2);
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 2));
            cellTitle.setCellValue("ic卡");
            cellTitle.setCellStyle(borderStyle);

            cellTitle = row2.createCell(3);
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 3, 3));
            cellTitle.setCellValue("抄表");
            cellTitle.setCellStyle(borderStyle);





            cellTitle = row.createCell(4);
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 4, 5));
            cellTitle.setCellValue("电费");
            cellTitle.setCellStyle(borderStyle);

            cellTitle = row2.createCell(4);
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 4, 4));
            cellTitle.setCellValue("ic卡");
            cellTitle.setCellStyle(borderStyle);

            cellTitle = row2.createCell(5);
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 5, 5));
            cellTitle.setCellValue("抄表");
            cellTitle.setCellStyle(borderStyle);





            cellTitle = row.createCell(6);
            sheet.addMergedRegion(new CellRangeAddress(0, 1, 6, 6));
            cellTitle.setCellValue("车位费");
            cellTitle.setCellStyle(borderStyle);

            cellTitle = row.createCell(7);
            sheet.addMergedRegion(new CellRangeAddress(0, 1, 7, 7));
            cellTitle.setCellValue("押金");
            cellTitle.setCellStyle(borderStyle);
            int cell=7;
            for(String s:extList){
                cell++;
                cellTitle = row.createCell(cell);
                sheet.addMergedRegion(new CellRangeAddress(0, 1, cell, cell));
                cellTitle.setCellValue(s);
                cellTitle.setCellStyle(borderStyle);
            }
            for(SysVarDO s:extraList){
                cell++;
                cellTitle = row.createCell(cell);
                sheet.addMergedRegion(new CellRangeAddress(0, 1, cell, cell));
                cellTitle.setCellValue(s.getVarValue());
                cellTitle.setCellStyle(borderStyle);
            }
            cell++;
            cellTitle = row.createCell(cell);
            sheet.addMergedRegion(new CellRangeAddress(0, 1, cell, cell));
            cellTitle.setCellValue("自行减免");
            cellTitle.setCellStyle(borderStyle);

            cell++;
            cellTitle = row.createCell(cell);
            sheet.addMergedRegion(new CellRangeAddress(0, 1, cell, cell));
            cellTitle.setCellValue("地产减免");
            cellTitle.setCellStyle(borderStyle);

            cell++;
            cellTitle = row.createCell(cell);
            sheet.addMergedRegion(new CellRangeAddress(0, 1, cell, cell));
            cellTitle.setCellValue("合计");
            cellTitle.setCellStyle(borderStyle);

            for(String s:accountEstateTypeList){
                cell++;
                cellTitle = row.createCell(cell);
                sheet.addMergedRegion(new CellRangeAddress(0, 1, cell, cell));
                cellTitle.setCellValue(s);
                cellTitle.setCellStyle(borderStyle);
            }

            for(String s:extList){
                cell++;
                cellTitle = row.createCell(cell);
                sheet.addMergedRegion(new CellRangeAddress(0, 1, cell, cell));
                cellTitle.setCellValue("退" + s);
                cellTitle.setCellStyle(borderStyle);
            }

            Iterator<Map.Entry<String, BillStatVO>> iterator = map.entrySet().iterator();
            int rowNum = 1;
            while(iterator.hasNext()){
                rowNum++;
                Map.Entry<String, BillStatVO> next = iterator.next();
                String key = next.getKey();
                BillStatVO billStatVO = next.getValue();
                row = sheet.createRow(rowNum);
                cellTitle = row.createCell(0);
                cellTitle.setCellValue(key);
                cellTitle.setCellStyle(borderStyle);

                cellTitle = row.createCell(1);
                cellTitle.setCellValue(PriceUtil.formatCNY(billStatVO.getEstatePayTotal()));
                cellTitle.setCellStyle(borderStyle);

                cellTitle = row.createCell(2);
                cellTitle.setCellValue(PriceUtil.formatCNY(billStatVO.getWaterICPayTotal()));
                cellTitle.setCellStyle(borderStyle);

                cellTitle = row.createCell(3);
                cellTitle.setCellValue(PriceUtil.formatCNY(billStatVO.getWaterMeterPayTotal()));
                cellTitle.setCellStyle(borderStyle);

                cellTitle = row.createCell(4);
                cellTitle.setCellValue(PriceUtil.formatCNY(billStatVO.getElecICPayTotal()));
                cellTitle.setCellStyle(borderStyle);

                cellTitle = row.createCell(5);
                cellTitle.setCellValue(PriceUtil.formatCNY(billStatVO.getElecMeterPayTotal()));
                cellTitle.setCellStyle(borderStyle);

                cellTitle = row.createCell(6);
                cellTitle.setCellValue(PriceUtil.formatCNY(billStatVO.getParkingPayTotal()));
                cellTitle.setCellStyle(borderStyle);

                cellTitle = row.createCell(7);
                cellTitle.setCellValue(PriceUtil.formatCNY(billStatVO.getDepositPayTotal()));
                cellTitle.setCellStyle(borderStyle);
                cell = 7;

                for(StatObj statObj:billStatVO.getStatObjList()){
                    cell++;
                    cellTitle = row.createCell(cell);
                    cellTitle.setCellValue(PriceUtil.formatCNY(statObj.getFeeTotal()));
                    cellTitle.setCellStyle(borderStyle);
                }
                for(StatObj statObj:billStatVO.getExtraObjList()){
                    cell++;
                    cellTitle = row.createCell(cell);
                    cellTitle.setCellValue(PriceUtil.formatCNY(statObj.getFeeTotal()));
                    cellTitle.setCellStyle(borderStyle);
                }
                cell++;
                cellTitle = row.createCell(cell);
                cellTitle.setCellValue(PriceUtil.formatCNY(billStatVO.getSelfDiscount()));
                cellTitle.setCellStyle(borderStyle);

                cell++;
                cellTitle = row.createCell(cell);
                cellTitle.setCellValue(PriceUtil.formatCNY(billStatVO.getDichanDiscount()));
                cellTitle.setCellStyle(borderStyle);

                cell++;
                cellTitle = row.createCell(cell);
                cellTitle.setCellValue(PriceUtil.formatCNY(billStatVO.getFeeTotal()));
                cellTitle.setCellStyle(borderStyle);

                for(StatObj statObj:billStatVO.getAccountEstateObjList()){
                    cell++;
                    cellTitle = row.createCell(cell);
                    cellTitle.setCellValue(PriceUtil.formatCNY(statObj.getFeeTotal()));
                    cellTitle.setCellStyle(borderStyle);
                }
                for(StatObj statObj:billStatVO.getRefundObjList()){
                    cell++;
                    cellTitle = row.createCell(cell);
                    cellTitle.setCellValue(PriceUtil.formatCNY(statObj.getFeeTotal()));
                    cellTitle.setCellStyle(borderStyle);
                }

            }



            outputStream = response.getOutputStream();


            try {
                workbook.write(outputStream);
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }


            outputStream.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                if(outputStream!=null) {
                    outputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }



    @RequestMapping(value = "export/stat.do", method = RequestMethod.GET)
    public void exportStat(HttpServletResponse response, ModelMap modelMap,StatFeeForm statFeeForm) {
        BizResult stat = billStatBiz.stat(statFeeForm);
        Map<String, BillStatVO> map = (Map<String, BillStatVO>)stat.data.get("statMap");
        List<String> extList = (List<String>)stat.data.get("extList");
        List<SysVarDO> extraList = (List<SysVarDO>)stat.data.get("extraList");
        String filedisplay = null;
        try {
            filedisplay = URLEncoder.encode("收入汇总.xls", "UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        response.setContentType("application/x-download; charset=UTF-8");
        response.addHeader("Content-Disposition", "attachment; filename=" + filedisplay);

        ServletOutputStream outputStream = null;
        try {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet("收入汇总");

            HSSFCellStyle cellCenterStyle =  workbook.createCellStyle();// 设置表头样式
            cellCenterStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置居中

            HSSFCellStyle borderStyle =  workbook.createCellStyle();// 设置表头样式
            borderStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 设置居中
            borderStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
            borderStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
            borderStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
            borderStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框


            HSSFRow row = sheet.createRow(0);
            HSSFRow row2 = sheet.createRow(1);
            HSSFCell cellTitle = row.createCell(0);
            sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
            cellTitle.setCellValue("日期");
            cellTitle.setCellStyle(borderStyle);

            cellTitle = row.createCell(1);
            sheet.addMergedRegion(new CellRangeAddress(0, 1, 1, 1));
            cellTitle.setCellValue("物业费");
            cellTitle.setCellStyle(borderStyle);

            cellTitle = row.createCell(2);
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 2, 3));
            cellTitle.setCellStyle(borderStyle);
            cellTitle.setCellValue("水费");

            cellTitle = row2.createCell(2);
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 2));
            cellTitle.setCellValue("ic卡");
            cellTitle.setCellStyle(borderStyle);

            cellTitle = row2.createCell(3);
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 3, 3));
            cellTitle.setCellValue("抄表");
            cellTitle.setCellStyle(borderStyle);


            cellTitle = row.createCell(4);
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 4, 5));
            cellTitle.setCellValue("电费");
            cellTitle.setCellStyle(borderStyle);

            cellTitle = row2.createCell(4);
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 4, 4));
            cellTitle.setCellValue("ic卡");
            cellTitle.setCellStyle(borderStyle);

            cellTitle = row2.createCell(5);
            sheet.addMergedRegion(new CellRangeAddress(1, 1, 5, 5));
            cellTitle.setCellValue("抄表");
            cellTitle.setCellStyle(borderStyle);

            cellTitle = row.createCell(6);
            sheet.addMergedRegion(new CellRangeAddress(0, 1, 6, 6));
            cellTitle.setCellValue("车位费");
            cellTitle.setCellStyle(borderStyle);

            cellTitle = row.createCell(7);
            sheet.addMergedRegion(new CellRangeAddress(0, 1, 7, 7));
            cellTitle.setCellValue("押金");
            cellTitle.setCellStyle(borderStyle);
            int cell=7;
            for(String s:extList){
                cell++;
                cellTitle = row.createCell(cell);
                sheet.addMergedRegion(new CellRangeAddress(0, 1, cell, cell));
                cellTitle.setCellValue(s);
                cellTitle.setCellStyle(borderStyle);
            }
            for(SysVarDO s:extraList){
                cell++;
                cellTitle = row.createCell(cell);
                sheet.addMergedRegion(new CellRangeAddress(0, 1, cell, cell));
                cellTitle.setCellValue(s.getVarValue());
                cellTitle.setCellStyle(borderStyle);
            }
            cell++;
            cellTitle = row.createCell(cell);
            sheet.addMergedRegion(new CellRangeAddress(0, 1, cell, cell));
            cellTitle.setCellValue("自行减免");
            cellTitle.setCellStyle(borderStyle);

            cell++;
            cellTitle = row.createCell(cell);
            sheet.addMergedRegion(new CellRangeAddress(0, 1, cell, cell));
            cellTitle.setCellValue("地产减免");
            cellTitle.setCellStyle(borderStyle);



            Iterator<Map.Entry<String, BillStatVO>> iterator = map.entrySet().iterator();
            int rowNum = 1;
            while(iterator.hasNext()){
                rowNum++;
                Map.Entry<String, BillStatVO> next = iterator.next();
                String key = next.getKey();
                BillStatVO billStatVO = next.getValue();
                row = sheet.createRow(rowNum);
                cellTitle = row.createCell(0);
                cellTitle.setCellValue(key);
                cellTitle.setCellStyle(borderStyle);

                cellTitle = row.createCell(1);
                cellTitle.setCellValue(PriceUtil.formatCNY(billStatVO.getEstatePayTotal()));
                cellTitle.setCellStyle(borderStyle);

                cellTitle = row.createCell(2);
                cellTitle.setCellValue(PriceUtil.formatCNY(billStatVO.getWaterICPayTotal()));
                cellTitle.setCellStyle(borderStyle);

                cellTitle = row.createCell(3);
                cellTitle.setCellValue(PriceUtil.formatCNY(billStatVO.getWaterMeterPayTotal()));
                cellTitle.setCellStyle(borderStyle);

                cellTitle = row.createCell(4);
                cellTitle.setCellValue(PriceUtil.formatCNY(billStatVO.getElecICPayTotal()));
                cellTitle.setCellStyle(borderStyle);

                cellTitle = row.createCell(5);
                cellTitle.setCellValue(PriceUtil.formatCNY(billStatVO.getElecMeterPayTotal()));
                cellTitle.setCellStyle(borderStyle);

                cellTitle = row.createCell(6);
                cellTitle.setCellValue(PriceUtil.formatCNY(billStatVO.getParkingPayTotal()));
                cellTitle.setCellStyle(borderStyle);

                cellTitle = row.createCell(7);
                cellTitle.setCellValue(PriceUtil.formatCNY(billStatVO.getDepositPayTotal()));
                cellTitle.setCellStyle(borderStyle);
                cell = 7;

                for(StatObj statObj:billStatVO.getStatObjList()){
                    cell++;
                    cellTitle = row.createCell(cell);
                    cellTitle.setCellValue(PriceUtil.formatCNY(statObj.getFeeTotal()));
                    cellTitle.setCellStyle(borderStyle);
                }
                for(StatObj statObj:billStatVO.getExtraObjList()){
                    cell++;
                    cellTitle = row.createCell(cell);
                    cellTitle.setCellValue(PriceUtil.formatCNY(statObj.getFeeTotal()));
                    cellTitle.setCellStyle(borderStyle);
                }
                cell++;
                cellTitle = row.createCell(cell);
                cellTitle.setCellValue(PriceUtil.formatCNY(billStatVO.getSelfDiscount()));
                cellTitle.setCellStyle(borderStyle);

                cell++;
                cellTitle = row.createCell(cell);
                cellTitle.setCellValue(PriceUtil.formatCNY(billStatVO.getDichanDiscount()));
                cellTitle.setCellStyle(borderStyle);



            }



            outputStream = response.getOutputStream();


            try {
                workbook.write(outputStream);
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }


            outputStream.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }
}
